set hive.exec.dynamic.partition=true;
set hive.exec.dynamic.partition.mode=nonstrict;
set hive.exec.max.dynamic.partitions=1000;
set hive.exec.max.dynamic.partitions.pernode=1000;


with  tmp_dm_reback_timely_rate_sum as (
     select
         t.site_code as  actual_network_code
        ,max(site_name) actual_network_name
        ,sum(case when type_flg = 1 and register_type = 2 and (is_timely = 1 or is_timely = 2) then 1
                  when type_flg = 2 and register_type = 1 and (is_timely = 1 or is_timely = 2) then 1
                  else 0 end  )       as  waybill_num         --件量

        
        ,sum( case  when type_flg = 1 and register_type = 2 and is_timely = 1 then 1 
                    when type_flg = 2 and register_type = 1 and is_timely = 1 then 1
                    else 0 end  )       as is_timely_true     --类型分类,及时
        
        ,sum(case when type_flg = 1 and register_type = 2 and is_timely = 2 then 1 
                  when type_flg = 2 and register_type = 1 and is_timely = 2 then 1
                  else 0 end   )       as is_timely_false    --类型分类,不及时
        
        ,sum(case when type_flg = 1 and register_type = 2 and is_timely = 2 then 1 
                  when type_flg = 2 and register_type = 1 and is_timely = 2 then 1
                  else 0 end   )  *0.3   as waybill_money      --罚款金额
        
        ,sum(case when t.duty_cal=1 and (is_timely = 1 or is_timely = 2) then 1 else 0 end )  as duty_cal_1  --1未打印面单
        ,sum(case when t.duty_cal=2 and (is_timely = 1 or is_timely = 2) then 1 else 0 end )  as duty_cal_2  --2已打印未发出
        ,sum(case when t.duty_cal=3 and (is_timely = 1 or is_timely = 2) then 1 else 0 end )  as duty_cal_3  --3已打印漏扫发件
        ,sum(case when t.duty_cal=4 and (is_timely = 1 or is_timely = 2) then 1 else 0 end )  as duty_cal_4  --4非应操作网点发出
        ,sum(case when t.duty_cal=5 and (is_timely = 1 or is_timely = 2) then 1 else 0 end )  as duty_cal_5  --5下一站晚到
        ,sum(case when t.duty_cal=6 and (is_timely = 1 or is_timely = 2) then 1 else 0 end )  as duty_cal_6  --6虚假发件
        ,t.type_flg
        ,sum(case when is_timely = 3 then 1 else 0 end ) as isTimely
        ,t.dt
    from jms_dm.dm_reback_timely_rate t
    where t.dt>=date_add('{{ execution_date | cst_ds }}',-30)
    group by t.dt,t.site_code,t.type_flg

)

insert overwrite table jms_dm.dm_reback_timely_rate_sum partition(dt)
select 
 t.dt as date_time   
,t.actual_network_code
,t.actual_network_name
,sum(t.waybill_num)     over(partition by t.dt,t.actual_network_code)     as total_num    
,sum(t.is_timely_false) over(partition by t.dt,t.actual_network_code)*0.3 as total_money   
,sum(t.is_timely_true)  over(partition by t.dt,t.actual_network_code)     as total_is_timely_true 
,sum(t.is_timely_false) over(partition by t.dt,t.actual_network_code)     as total_is_timely_false 
,t.is_timely_true     --类型分类,及时
,t.is_timely_false    --类型分类,不及时
,t.waybill_num              --件量
,t.waybill_money    --罚款金额
,t.duty_cal_1  --1未打印面单 
,t.duty_cal_2  --2已打印未发出
,t.duty_cal_3  --3已打印漏扫发件
,t.duty_cal_4  --4非应操作网点发出
,t.duty_cal_5  --5下一站晚到
,t.duty_cal_6  --6虚假发件
,t.type_flg
,t.isTimely
,t.dt
from tmp_dm_reback_timely_rate_sum t
distribute by pmod(hash(rand()), 3)
;


